﻿Public Class DataManager

    Private Shared _dataset As DataModel.PmtDataset
    Private _AnneeReference As Double = 2011
    Private _NombreProjection As Double = 5


    Function LoadTableFromCache(tableName As String) As DataTable

    End Function

    Function LoadTableFromServer(tableName As String, connection As Odbc.OdbcConnection) As DataTable
        Dim adapter = New Odbc.OdbcDataAdapter("SELECT * FROM " & tableName, connection)
        Dim result = New DataTable()
        adapter.Fill(result)
        Return result
    End Function

    Function LoadTableFromServer(tableName As String, connection As OleDb.OleDbConnection) As DataTable

    End Function

    Function LoadTableFromServer(tableName As String, connection As SqlClient.SqlConnection) As DataTable
        Dim adapter = New SqlClient.SqlDataAdapter("SELECT * FROM " & tableName, connection)
        Dim result = New DataTable()
        adapter.Fill(result)
        Return result
    End Function

    Sub SaveToCache(tablename As String, data As DataTable)

    End Sub

    Public ReadOnly Property Dataset As DataModel.PmtDataset
        Get
            If _dataset Is Nothing Then
                Dim datafile = IO.Path.Combine(IO.Path.GetDirectoryName(Me.GetType().Assembly.Location), "Cache\PmtActif.data")
                If IO.File.Exists(datafile) Then
                    _dataset = New DataModel.PmtDataset()
                    _dataset.ReadXml(datafile)
                Else
                    _dataset = New DataModel.PmtDataset()
                    PopulateDataSetFromSqlServer(_dataset)
                    _dataset.WriteXml(datafile)
                End If

                CreateBigTable()

            End If

            Return _dataset
        End Get
    End Property

    Public ReadOnly Property AnneeReference As Double
        Get
            Return _AnneeReference
        End Get
    End Property

    Public ReadOnly Property NombreProjection As Double
        Get
            Return _NombreProjection
        End Get
    End Property

    Friend Sub CreateBigTable()
        Dim q0 = Enumerable.Range(_AnneeReference, _NombreProjection)

        'Nouveaux groupes sans notion de  génération
        Dim qsg1 = From elt In _dataset.NouveauGroupe, elt2 In q0
                   Where elt.Avec_Generation = 0 And elt2 >= elt.Annee_Reference
                   Order By elt.Groupe, elt.Code_Garantie, elt2
                   Select elt.Groupe, elt.Code_Garantie, Generation = 0.0, Annee = CDbl(elt2), Annee_Reference = elt.Annee_Reference

        'Nouveaux groupes avec notion de  génération
        Dim qag1 = From elt In _dataset.NouveauGroupe, elt2 In q0, elt3 In q0
                   Where elt.Avec_Generation <> 0 And elt3 >= elt.Annee_Reference And elt2 >= elt.Annee_Reference And elt2 <= elt3
                   Order By elt.Groupe, elt.Code_Garantie, elt2, elt3
                   Select elt.Groupe, elt.Code_Garantie, Generation = CDbl(elt2), Annee = CDbl(elt3), Annee_Reference = elt.Annee_Reference

        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                        Join crtpmt In _dataset.CorrespondanceCrtPmt
                           On histo.Code_Banque Equals crtpmt.Code_Banque _
                           And histo.Code_Police Equals crtpmt.Code_Police _
                           And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                        Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                           On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                        Where histo.Annee_Arrete = AnneeReference _
                          And histo.Mois_Arrete = 12 _
                          And Not crtpmt.IsGroupeNull() _
                          And Not String.IsNullOrEmpty(crtpmt.Groupe)


        Dim qhistosminmaxgen = From elt In qhistopmt
                               Group By elt.crtpmt.Groupe, elt.histo.Code_Garantie
                               Into Min_Generation = Min(elt.gentarifcomp.Generation_Comptable), _
                                    Max_Generation = Max(elt.gentarifcomp.Generation_Comptable)

        Dim qclehisto = From elt In qhistopmt
                        Select elt.crtpmt.Groupe, elt.histo.Code_Garantie, elt.gentarifcomp.Generation_Comptable Distinct

        Dim qclehistoag = From elt In qhistopmt
                          Where elt.gentarifcomp.Generation_Comptable > 0.0
                          Select elt.crtpmt.Groupe, elt.histo.Code_Garantie Distinct

        Dim qasg = From elt In qclehisto, elt2 In q0
                   Order By elt.Groupe, elt.Code_Garantie, elt.Generation_Comptable, elt2
                   Select elt.Groupe, elt.Code_Garantie, elt.Generation_Comptable, Annee = CDbl(elt2), Annee_Reference = _AnneeReference

        Dim qag2 = From elt In qclehistoag, elt2 In q0, elt3 In q0
                   Where elt3 > _AnneeReference And elt2 > _AnneeReference And elt2 <= elt3
                   Order By elt.Groupe, elt.Code_Garantie, elt2, elt3
                   Select elt.Groupe, elt.Code_Garantie, Generation = CDbl(elt2), Annee = CDbl(elt3), Annee_Reference = _AnneeReference

        _dataset.ReferenceProjection.Rows.Clear()

        For Each elt In qsg1
            _dataset.ReferenceProjection.AddReferenceProjectionRow( _
                elt.Groupe, elt.Code_Garantie, elt.Generation, elt.Annee, elt.Annee_Reference)
        Next

        For Each elt In qag1
            _dataset.ReferenceProjection.AddReferenceProjectionRow( _
                elt.Groupe, elt.Code_Garantie, elt.Generation, elt.Annee, elt.Annee_Reference)
        Next

        For Each elt In qasg
            _dataset.ReferenceProjection.AddReferenceProjectionRow( _
              elt.Groupe, elt.Code_Garantie, elt.Generation_Comptable, elt.Annee, elt.Annee_Reference)
        Next

        For Each elt In qag2
            _dataset.ReferenceProjection.AddReferenceProjectionRow( _
             elt.Groupe, elt.Code_Garantie, elt.Generation, elt.Annee, elt.Annee_Reference)
        Next


    End Sub

    Private Sub PopulateDataSetFromSqlServer(dset As DataModel.PmtDataset)
        Dim TestConnectionString = "Data Source=VIVEI-10\SQLEXPRESS;Initial Catalog=TESTPMT;Integrated Security=True;Pooling=False"

        Dim adapter As SqlClient.SqlDataAdapter

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM CRTPMT", TestConnectionString)
        adapter.Fill(dset.CorrespondanceCrtPmt)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM PERIMETRE", TestConnectionString)
        adapter.Fill(dset.Perimetre)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM PMTPERIMETRE", TestConnectionString)
        adapter.Fill(dset.ReferencePMTPerimetre)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM NVLGEN", TestConnectionString)
        adapter.Fill(dset.NouvelleGeneration)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM DEROULE", TestConnectionString)
        adapter.Fill(dset.Deroule)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM REASS", TestConnectionString)
        adapter.Fill(dset.Reassurance)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM TXCOMFRAIS", TestConnectionString)
        adapter.Fill(dset.CommissionsFrais)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM NVGROUPES", TestConnectionString)
        adapter.Fill(dset.NouveauGroupe)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM HISTOCRT", TestConnectionString)
        adapter.Fill(dset.ReferenceCrt)

        adapter = New SqlClient.SqlDataAdapter("SELECT * FROM GENTARIFCOMPT", TestConnectionString)
        adapter.Fill(dset.CorrespondanceGenTarifComptable)

    End Sub

    Function CacheNeedUpdate(tablename As String, Optional versionhandler As Func(Of String, Boolean) = Nothing) As Boolean
        If versionhandler Is Nothing Then Return False
        Return versionhandler(tablename)
    End Function

    Public Sub Recalculer_Table_Reference_Projection()
        CreateBigTable()
    End Sub

    Public Sub Recalculer_Table_Taux_Com()
        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                        Join crtpmt In _dataset.CorrespondanceCrtPmt
                           On histo.Code_Banque Equals crtpmt.Code_Banque _
                           And histo.Code_Police Equals crtpmt.Code_Police _
                           And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                        Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                           On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                        Where histo.Annee_Arrete = AnneeReference _
                          And histo.Mois_Arrete = 12 _
                          And Not crtpmt.IsGroupeNull() _
                          And Not String.IsNullOrEmpty(crtpmt.Groupe)


        Dim qcom = From elt In qhistopmt
                   Group By elt.crtpmt.Groupe, elt.histo.Code_Garantie, elt.gentarifcomp.Generation_Comptable
                   Into combp = Sum(elt.histo.COMMISSIONS_BP_ESTIMEES + elt.histo.COMMISSIONS_BP_REELLES), _
                       comcbp = Sum(elt.histo.COMMISSIONS_CBP_ESTIMEES + elt.histo.COMMISSIONS_CBP_REELLES), _
                       prime = Sum(elt.histo.PRIMES_HT_ESTIMEES + elt.histo.PRIMES_HT_REELLES)

        Dim qcomrow = From elt In qcom
                      Select New Object() { _
                          elt.Groupe, _
                          elt.Code_Garantie, _
                          elt.Generation_Comptable, _
                          Math.Round(elt.comcbp / elt.prime, 4), _
                          Math.Round(elt.combp / elt.prime, 4), _
                          0.0}

        _dataset.CommissionsFrais_HistoCRT.Rows.Clear()

        For Each elt In qcomrow
            _dataset.CommissionsFrais_HistoCRT.Rows.Add(elt)
        Next
    End Sub

    Public Sub Recalculer_Table_Deroule_Historique_Crt()
        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                        Join crtpmt In _dataset.CorrespondanceCrtPmt
                         On histo.Code_Banque Equals crtpmt.Code_Banque _
                         And histo.Code_Police Equals crtpmt.Code_Police _
                         And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                       Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                         On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                       Where histo.Mois_Arrete = 12 _
                        And Not crtpmt.IsGroupeNull() _
                        And Not String.IsNullOrEmpty(crtpmt.Groupe) _
                        And gentarifcomp.Generation_Comptable > 0
                      Select crtpmt.Groupe, _
                             histo.Code_Garantie, _
                             Deroule = histo.Annee_Arrete - gentarifcomp.Generation_Comptable, _
                             Primes = histo.PRIMES_HT_ESTIMEES + histo.PRIMES_HT_REELLES, _
                             Sinistres = histo.MONTANT_SINISTRES_REGLES, _
                             Primes_Risque = histo.PRIMES_HT_ESTIMEES + histo.PRIMES_HT_REELLES _
                                             - histo.COMMISSIONS_BP_ESTIMEES - histo.COMMISSIONS_BP_REELLES _
                                             - histo.COMMISSIONS_CBP_ESTIMEES - histo.COMMISSIONS_CBP_REELLES _
                                             - histo.FRAIS_SELECTION_MEDICALE, _
                             Charges_Sinistre = histo.MONTANT_SINISTRES_REGLES _
                                                + histo.PROVISION_SIN_CON_FIN + histo.SAP + histo.PROVISION_SIN_INC_FIN_ACTUELLE _
                                                - histo.PROVISION_SIN_CON_DEB - histo.PROVISION_SIN_INC_DEB


        Dim qhistopmtgroupe = From elt In qhistopmt.ToList()
                              Group By elt.Groupe, elt.Code_Garantie, elt.Deroule
                              Into Primes = Sum(elt.Primes), _
                                   Sinistres = Sum(elt.Sinistres), _
                                   Charges_Sinistre = Sum(elt.Charges_Sinistre), _
                                   Primes_Risque = Sum(elt.Primes_Risque)

        Dim qhistoderoule = From elt In qhistopmtgroupe
                            Join elt2 In qhistopmtgroupe
                            On elt.Groupe Equals elt2.Groupe And elt.Code_Garantie Equals elt2.Code_Garantie And elt.Deroule Equals (elt2.Deroule + 1)
                            Where elt.Deroule > 0
                            Order By elt.Groupe, elt.Code_Garantie, elt.Deroule
                            Select elt.Groupe, elt.Code_Garantie, elt.Deroule,
                                   Taux_Deroule_Prime = elt.Primes / elt2.Primes,
                                   Taux_Deroule_Sinistre = elt.Sinistres / elt2.Sinistres,
                                   CS_sur_PR = elt.Charges_Sinistre / elt.Primes_Risque

        Dataset.Deroule_HistoCrt.Rows.Clear()

        For Each elt In qhistoderoule
            Dataset.Deroule_HistoCrt.AddDeroule_HistoCrtRow(elt.Groupe, elt.Code_Garantie, elt.Deroule, elt.Taux_Deroule_Prime, elt.Taux_Deroule_Sinistre, elt.CS_sur_PR)
        Next
    End Sub

    Public Sub Recalculer_Table_Nouvelle_Gen_Observe()
        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                   Join crtpmt In _dataset.CorrespondanceCrtPmt
                      On histo.Code_Banque Equals crtpmt.Code_Banque _
                      And histo.Code_Police Equals crtpmt.Code_Police _
                      And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                   Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                      On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                   Where (histo.Annee_Arrete = AnneeReference Or histo.Annee_Arrete = (AnneeReference - 1)) _
                     And histo.Mois_Arrete = 12 _
                     And Not crtpmt.IsGroupeNull() _
                     And Not String.IsNullOrEmpty(crtpmt.Groupe) _
                     And gentarifcomp.Generation_Comptable > 0.0 _
                     And (histo.Annee_Arrete - gentarifcomp.Generation_Comptable) = 0.0

        Dim qanneref = From elt In qhistopmt
                       Where elt.histo.Annee_Arrete = AnneeReference
                       Group By elt.crtpmt.Groupe, elt.histo.Code_Garantie
                       Into Sum(elt.histo.PRIMES_HT_ESTIMEES + elt.histo.PRIMES_HT_REELLES)

        Dim qanneref_1 = From elt In qhistopmt
                       Where elt.histo.Annee_Arrete = (AnneeReference - 1)
                       Group By elt.crtpmt.Groupe, elt.histo.Code_Garantie
                       Into Sum(elt.histo.PRIMES_HT_ESTIMEES + elt.histo.PRIMES_HT_REELLES)

        Dim qnvlegen = From elt1 In qanneref, elt2 In qanneref_1
                       Where elt1.Groupe = elt2.Groupe And elt1.Code_Garantie = elt2.Code_Garantie
                       Select elt1.Groupe, elt1.Code_Garantie, Tx_Prime = elt1.Sum / elt2.Sum
    End Sub

    Public Sub Reinitialiser_Table_Deroule()
        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                      Join crtpmt In _dataset.CorrespondanceCrtPmt
                         On histo.Code_Banque Equals crtpmt.Code_Banque _
                         And histo.Code_Police Equals crtpmt.Code_Police _
                         And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                      Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                         On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                      Where histo.Annee_Arrete = AnneeReference _
                        And histo.Mois_Arrete = 12 _
                        And Not crtpmt.IsGroupeNull() _
                        And Not String.IsNullOrEmpty(crtpmt.Groupe)

        Dim qgroupescrt = From elt In qhistopmt Select elt.crtpmt.Groupe, elt.histo.Code_Garantie Distinct

        Dim deroules = Enumerable.Range(1, 20)

        '  Dim qderoules  = From elt In qgroupescrt , elt2 
    End Sub

    Public Function Test_Homogeneite_Groupe(groupecrt As String) As String
        Dim qhistopmt = From histo In _dataset.ReferenceCrt
                        Join crtpmt In _dataset.CorrespondanceCrtPmt
                           On histo.Code_Banque Equals crtpmt.Code_Banque _
                           And histo.Code_Police Equals crtpmt.Code_Police _
                           And histo.Sous_Categorie Equals crtpmt.Sous_Categorie
                        Join gentarifcomp In _dataset.CorrespondanceGenTarifComptable
                           On histo.Generation_Tarif Equals gentarifcomp.Generation_Tarif
                        Where crtpmt.Groupe = groupecrt

        Dim qminmaxgen = From elt In qhistopmt
                        Group By elt.crtpmt.Groupe
                        Into MinGen = Min(elt.gentarifcomp.Generation_Comptable),
                             MaxGen = Max(elt.gentarifcomp.Generation_Comptable)

        Dim result = qminmaxgen.FirstOrDefault()

        If result Is Nothing Then Return "Impossible de trouver le  groupe " & groupecrt

        If (result.MinGen > 0.0) Or (result.MaxGen <= 0.0) Then

            Return "Le groupe " & groupecrt & " est homogene " & vbCrLf & _
                   "La plus petite generation observée est : " & result.MinGen & vbCrLf & _
                   "La plus grande generation observée est : " & result.MaxGen

        Else
            Return "Le groupe " & groupecrt & " n'est pas homogene " & vbCrLf & _
                    "La plus petite generation observée est : " & result.MinGen & vbCrLf & _
                    "La plus grande generation observée est : " & result.MaxGen

        End If
    End Function

End Class
